package cn.dmrliu.edu.publish.mapper;

import cn.dmrliu.edu.publish.bean.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TrafficChannelStatsMapper {
    // 1. 各渠道独立访客数

    @Select("select \n" +
            "  ch,sum(uv_ct) uv_ct\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt),ch\n" +
            "order by uv_ct desc;")
    List<TrafficUvCtByCh> selectUvCt(@Param("date") Integer date);

    // 2. 各渠道会话数
    @Select("select \n" +
            "  ch,sum(sv_ct) sv_ct\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt),ch\n" +
            "order by sv_ct desc;")
    List<TrafficSvCtByCh> selectSvCt(@Param("date") Integer date);

    // 3. 各渠道会话平均浏览页面数
    @Select("select \n" +
            "  ch,\n" +
            "  sum(pv_ct)/sum(sv_ct) pv_per_session\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt),ch\n" +
            "order by pv_per_session desc;")
    List<TrafficPvPerSessionByCh> selectPvPerSession(@Param("date") Integer date);

    // 4. 各渠道会话平均停留时长
    @Select("select \n" +
            "  ch,\n" +
            "  sum(dur_sum)/sum(sv_ct) dur_per_session\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt),ch\n" +
            "order by dur_per_session desc;")
    List<TrafficDurPerSessionByCh> selectDurPerSession(@Param("date") Integer date);

    // 5. 各渠道跳出率
    @Select("select \n" +
            "  ch,\n" +
            "  sum(uj_ct)/sum(sv_ct) uj_rate\n" +
            "from dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt),ch\n" +
            "order by uj_rate desc;")
    List<TrafficUjRateByCh> selectUjRate(@Param("date") Integer date);

}
